package textfilewriter.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import textfilewriter.entity.dimension.Customer;
import textfilewriter.entity.dimension.Network;
import textfilewriter.entity.dimension.Portal;
import textfilewriter.entity.dimension.Product;
import textfilewriter.entity.dimension.Service;

public class DBProcess {

    private static final Logger logger = Logger.getLogger(DBProcess.class.getName());
    private DatabaseAccess databaseAccess;

    public DBProcess() {
        databaseAccess = DatabaseAccess.getInstance();
    }

    public List<Customer> getCustomers() {
        Connection connection = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        String sql = "select * from dim_customers";
        List<Customer> customers = null;
        try {
            connection = databaseAccess.getConnection();
            ps = connection.prepareStatement(sql);
            rs = ps.executeQuery();
            customers = new ArrayList<Customer>();
            while (rs.next()) {
                Customer customer = new Customer();
                customer.setCustId(rs.getLong("CUST_ID"));
                customer.setCustFirstName(rs.getString("CUST_FIRST_NAME"));
                customer.setCustLastName(rs.getString("CUST_LAST_NAME"));
                customer.setCustYearOfBirth(rs.getShort("CUST_YEAR_OF_BIRTH"));
                customer.setCustStreetAddress(rs.getString("CUST_STREET_ADDRESS"));
                customer.setCustCity(rs.getString("CUST_CITY"));
                customer.setCustStateProvince(rs.getString("CUST_STATE_PROVINCE"));
                customer.setCustIncomeLevel(rs.getString("CUST_INCOME_LEVEL"));
                customer.setCustEmail(rs.getString("CUST_EMAIL"));
                customer.setCustCountry(rs.getString("CUST_COUNTRY"));
                customer.setMsisdn(rs.getString("MSISDN"));


                customers.add(customer);
            }
        } catch (SQLException ex) {
            Logger.getLogger(DBProcess.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            databaseAccess.closeResultSet(rs);
            databaseAccess.closePreparedStatement(ps);
            databaseAccess.closeConnection(connection);
        }

        return customers;
    }

    public List<Product> getProducts() {
        Connection connection = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        String sql = "select * from dim_product";
        List<Product> resultList = null;
        try {
            connection = databaseAccess.getConnection();
            ps = connection.prepareStatement(sql);
            rs = ps.executeQuery();
            resultList = new ArrayList<Product>();
            while (rs.next()) {
                Product object = new Product();
                object.setProductId(rs.getLong("PRODUCT_ID"));
                object.setProductDesc(rs.getString("PRODUCT_DESC"));
                object.setProductName(rs.getString("PRODUCT_NAME"));
                object.setProductType(rs.getString("PRODUCT_TYPE"));

                resultList.add(object);
            }
        } catch (SQLException ex) {
            Logger.getLogger(DBProcess.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            databaseAccess.closeResultSet(rs);
            databaseAccess.closePreparedStatement(ps);
            databaseAccess.closeConnection(connection);
        }

        return resultList;
    }

    public List<Service> getServices() {
        Connection connection = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        String sql = "select * from dim_service";
        List<Service> resultList = null;
        try {
            connection = databaseAccess.getConnection();
            ps = connection.prepareStatement(sql);
            rs = ps.executeQuery();
            resultList = new ArrayList<Service>();
            while (rs.next()) {
                Service object = new Service();
                object.setServiceId(rs.getLong("SERVICE_ID"));
                object.setServiceName(rs.getString("SERVICE_NAME"));
                object.setServiceType(rs.getString("SERVICE_TYPE"));
                object.setServiceDesc(rs.getString("SERVICE_DESC"));
                object.setServiceSection(rs.getString("SERVICE_SECTION"));

                resultList.add(object);
            }
        } catch (SQLException ex) {
            Logger.getLogger(DBProcess.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            databaseAccess.closeResultSet(rs);
            databaseAccess.closePreparedStatement(ps);
            databaseAccess.closeConnection(connection);
        }

        return resultList;
    }

    public List<Network> getNetworks() {
        Connection connection = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        String sql = "select * from dim_network";
        List<Network> resultList = null;
        try {
            connection = databaseAccess.getConnection();
            ps = connection.prepareStatement(sql);
            rs = ps.executeQuery();
            resultList = new ArrayList<Network>();
            while (rs.next()) {
                Network object = new Network();
                object.setNetworkId(rs.getString("NETWORK_ID"));
                object.setNetworkName(rs.getString("NETWORK_NAME"));
                object.setBusyHour(rs.getLong("BUSY_HOUR"));
                object.setNetworkSegment(rs.getString("NETWORK_SEGMENT"));
                object.setNetworkChannel(rs.getLong("NETWORK_CHANNEL"));

                resultList.add(object);
            }
        } catch (SQLException ex) {
            Logger.getLogger(DBProcess.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            databaseAccess.closeResultSet(rs);
            databaseAccess.closePreparedStatement(ps);
            databaseAccess.closeConnection(connection);
        }

        return resultList;
    }

    public List<Portal> getPortals() {
        Connection connection = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        String sql = "select * from dim_portal";
        List<Portal> resultList = null;
        try {
            connection = databaseAccess.getConnection();
            ps = connection.prepareStatement(sql);
            rs = ps.executeQuery();
            resultList = new ArrayList<Portal>();
            while (rs.next()) {
                Portal object = new Portal();
                object.setPortalId(rs.getLong("PORTAL_ID"));
                object.setPortalName(rs.getString("PORTAL_NAME"));
                object.setPortalUrl(rs.getString("PORTAL_URL"));
                object.setPortalDesc(rs.getString("PORTAL_DESC"));

                resultList.add(object);
            }
        } catch (SQLException ex) {
            Logger.getLogger(DBProcess.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            databaseAccess.closeResultSet(rs);
            databaseAccess.closePreparedStatement(ps);
            databaseAccess.closeConnection(connection);
        }

        return resultList;
    }

    public void updateMsisdn(long id, String msisdn) {
        Connection connection = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        String sql = "update dim_customers set msisdn=? where cust_id=?";
        try {
            connection = databaseAccess.getConnection();
            ps = connection.prepareStatement(sql);
            ps.setString(1, msisdn);
            ps.setLong(2, id);
            ps.executeUpdate();
            databaseAccess.commitTransaction(connection);
        } catch (SQLException ex) {
            Logger.getLogger(DBProcess.class.getName()).log(Level.SEVERE, null, ex);
            databaseAccess.rollBackTransaction(connection);
        } finally {
            databaseAccess.closeResultSet(rs);
            databaseAccess.closePreparedStatement(ps);
            databaseAccess.closeConnection(connection);
        }
    }
//    public void updateAisMessage(List<MessageAisDTO> list) {
//        try {
//            if (con == null || con.isClosed()) {
//                con = getConnection();
//            }
//        } catch (SQLException e) {
//            logger.error(e.getMessage());
//        }
//        if (con == null) {
//            return;
//        }
//        Statement stmt = null;
//        int[] result = null;
//        try {
//            int size = list.size();
//            if (size > 0) {
//                stmt = con.createStatement();
//                for (int i = 0; i < size; i++) {
//                    MessageAisDTO msg = list.remove(0);
//                    String sql = createSqlCommand(msg);
//                    stmt.addBatch(sql);
//                }
//                result = stmt.executeBatch();
//                if (result != null) {
//                    if (result.length != size) {
//                        logger.error("Insert Action is not completed");
//                    }
//                }
//            }
//        } catch (SQLException se) {
//            logger.error("INSERT DB has been error: " + se.getMessage());
//        } finally {
//            try {
//                if (stmt != null) {
//                    stmt.close();
//                }
//                db.commitTransaction(con);
//            } catch (SQLException ex) {
//                logger.error(ex.getMessage());
//            }
//        }
//    }
//
//    private String createSqlCommand(MessageAisDTO msg) {
//        String result = "INSERT INTO ais_messages(MESSAGE_TEXT, SOURCE_ID, MESSAGE_ID, MMSI, IMO, COUNTRY, VESSEL_TYPE, DIM_A, DIM_B"
//                + ", DIM_C, DIM_D, DRAUGTH, ROT, SOG, COG, LONGITUDE, LATITUDE, NAVSTATUS, TRUE_HEADING, CALLSIGN, NAME, ETA, DESTINATION"
//                + ", RECEIVED_TIME, ADDED_TIME) VALUES('" + msg.getMsgText() + "'," + msg.getSourceId() + "," + msg.getMessageId() + "," + msg.getMmsi() + "," + msg.getImo() + "," + msg.getCountryCode() + "," + msg.getVasselType() + "," + msg.getDimA() + "," + msg.getDimB() + "," + msg.getDimC() + "," + msg.getDimD() + "," + msg.getDrougth() + "," + msg.getRot() + "," + msg.getSog() + "," + msg.getCog() + "," + msg.getLongitude() + "," + msg.getLatitude() + "," + msg.getNavigateStatus() + "," + msg.getTrueHeading() + ",'" + msg.getCallSign() + "','" + msg.getName() + "','" + msg.getEta().toString() + "','" + msg.getDestination() + "'," + "to_date('" + msg.getReceivedTime() + "','dd/mm/yyyy hh24:mi:ss'),sysdate" + ")";
//        return result;
//    }
//
//    private Connection getConnection() {
//        Connection cn = null;
//        int retry = 0;
//        while (cn == null && retry < 3) {
//            cn = db.getConnection();
//            if (cn != null) {
//                break;
//            }
//            retry++;
//            try {
//                Thread.sleep(5000); // Tam nghi 5s
//            } catch (InterruptedException e) {
//                logger.error("InterruptedException when waiting connection");
//            }
//        }
//        return cn;
//    }
//
//    private void addBatchInsertPropTbl(String propertyName,
//            String propertyDescription,
//            String agentName,
//            String propertyValue,
//            String propertyType) {
//
//        try {
//            ps.setString(1, propertyName);
//            ps.setString(2, propertyDescription);
//            ps.setString(3, agentName);
//            ps.setString(4, propertyValue);
//            ps.setString(5, propertyType);
//            ps.addBatch();
//        } catch (SQLException ex) {
//            logger.error(ex.getMessage());
//        }
//    }
//
//    private void insertAgentTbl(String agentName, String agentDesc, String agentType, String agentStatus) {
//        try {
//            if (ps != null) {
//                ps.close();
//            }
//        } catch (SQLException ex) {
//            logger.error(ex.getMessage());
//        }
//
//        try {
//            ps = con.prepareStatement(INSERT_MONITOR_AGENTS);
////            INSERT_MONITOR_AGENTS =
////            "insert into AIS_MONITOR_AGENTS(AGENT_NAME, AGENT_DESC, AGENT_TYPE, AGENT_STATUS) " +
////            "values(?, ?, ?, ?)";
//            ps.setString(1, agentName);
//            ps.setString(2, agentDesc);
//            ps.setString(3, agentType);
//            ps.setString(4, agentStatus);
//            ps.executeUpdate();
//
//        } catch (SQLException ex) {
//            logger.error(ex.getMessage());
//        } finally {
//            try {
//                if (ps != null) {
//                    ps.close();
//                }
//            } catch (SQLException ex) {
//                logger.error(ex.getMessage());
//            }
//        }
//
//    }
//
//    private void addBatchUpdatePropTbl(String propertyName, String agentName, String propertyValue, int updateCnt) {
//        try {
//            ps.setString(1, propertyValue);
//            ps.setInt(2, updateCnt);
//            ps.setString(3, agentName);
//            ps.setString(4, propertyName);
//            ps.addBatch();
//        } catch (SQLException ex) {
//            logger.error(ex.getMessage());
//        }
//    }
//
//    private void updateAgentTbl(String agentStatus, String agentName) {
//        try {
//            if (ps != null) {
//                ps.close();
//            }
//        } catch (SQLException ex) {
//            logger.error(ex.getMessage());
//        }
//
//        try {
//            ps = con.prepareStatement(UPDATE_MONITOR_AGENTS);
////            UPDATE_MONITOR_AGENTS =
////            "update AIS_MONITOR_AGENTS " +
////            "set AGENT_STATUS=? " +
////            "where AGENT_NAME=?";
//            ps.setString(1, agentStatus);
//            ps.setString(2, agentName);
//            ps.executeUpdate();
//        } catch (SQLException ex) {
//            logger.error(ex.getMessage());
//        } finally {
//            try {
//                if (ps != null) {
//                    ps.close();
//                }
//            } catch (SQLException ex) {
//                logger.error(ex.getMessage());
//            }
//        }
//
//    }
//
//    public void insertDB(MonitorMessage msg) {
//        try {
//            if (con == null || con.isClosed()) {
//                con = getConnection();
//            }
//        } catch (SQLException e) {
//            logger.error(e.getMessage());
//        }
//        if (con == null) {
//            return;
//        }
//        String cpuFree = String.valueOf(msg.getCPU());
//        String objName = msg.getObjectName();
//
//        PreparedStatement ps2 = null;
//        ResultSet rs = null;
//
//        String strSQL = "";
//
//        try {
//            strSQL = "select * from AIS_MONITOR_PROPERTIES where AGENT_NAME = ?";
//            ps2 = con.prepareStatement(strSQL);
//            ps2.setString(1, objName);
//            rs = ps2.executeQuery();
//
//            try {
//                if (ps != null) {
//                    ps.close();
//                }
//            } catch (SQLException ex) {
//                logger.error(ex.getMessage());
//            }
//
//            if (rs.next()) {
//                int updateCnt = rs.getInt("UPDATED_COUNT") + 1;
//
//                logger.info("===Updating AIS_MONITOR_AGENTS===");
//                updateAgentTbl(String.valueOf(msg.getStatus()), objName);
//                logger.info("===Done===");
//
//                ps = con.prepareStatement(UPDATE_MONITOR_PROPPERTIES);
//                logger.info("===Updating AIS_MONITOR_PROPERTIES===");
//                addBatchUpdatePropTbl(CPU, objName, cpuFree, updateCnt);
//                addBatchUpdatePropTbl(RAM_TOTAL, objName, String.valueOf(msg.getRAMTotal()), updateCnt);
//                addBatchUpdatePropTbl(RAM_FREE, objName, String.valueOf(msg.getRAMFree()), updateCnt);
//                addBatchUpdatePropTbl(RAM_USED, objName, String.valueOf(msg.getRAMUsed()), updateCnt);
//                addBatchUpdatePropTbl(HDD_TOTAL, objName, String.valueOf(msg.getHDDTotal()), updateCnt);
//                addBatchUpdatePropTbl(HDD_FREE, objName, String.valueOf(msg.getRAMFree()), updateCnt);
//                addBatchUpdatePropTbl(HDD_USED, objName, String.valueOf(msg.getRAMUsed()), updateCnt);
//                addBatchUpdatePropTbl(IP, objName, msg.getMonitorIP(), updateCnt);
//                addBatchUpdatePropTbl(SPEED, objName, String.valueOf(msg.getAISSpeed()), updateCnt);
//                try {
//                    ps.executeBatch();
//                    logger.info("===Updated Successful===");
//                } catch (Exception ex) {
//                    logger.error("===Update failed===");
//                    logger.error(ex.getMessage());
//                }
//
//
//            } else {
//                logger.info("===Inserting AIS_MONITOR_AGENTS===");
//                insertAgentTbl(objName, "", msg.getObjectType(), String.valueOf(msg.getStatus()));
//                logger.info("===Done===");
//
//                ps = con.prepareStatement(INSERT_MONITOR_PROPPERTIES);
//                logger.info("===Inserting AIS_MONITOR_PROPERTIES===");
//                addBatchInsertPropTbl(CPU, CPU_DESCRIPTION, objName, cpuFree, "Integer");
//                addBatchInsertPropTbl(RAM_TOTAL, RAM_TOTAL_DESCRIPTION, objName, String.valueOf(msg.getRAMTotal()), INTEGER_TYPE);
//                addBatchInsertPropTbl(RAM_FREE, RAM_FREE_DESCRIPTION, objName, String.valueOf(msg.getRAMFree()), INTEGER_TYPE);
//                addBatchInsertPropTbl(RAM_USED, RAM_USED_DESCRIPTION, objName, String.valueOf(msg.getRAMUsed()), INTEGER_TYPE);
//                addBatchInsertPropTbl(HDD_TOTAL, HDD_TOTAL_DESCRIPTION, objName, String.valueOf(msg.getHDDTotal()), INTEGER_TYPE);
//                addBatchInsertPropTbl(HDD_FREE, HDD_FREE_DESCRIPTION, objName, String.valueOf(msg.getRAMFree()), INTEGER_TYPE);
//                addBatchInsertPropTbl(HDD_USED, HDD_USED_DESCRIPTION, objName, String.valueOf(msg.getRAMUsed()), INTEGER_TYPE);
//                addBatchInsertPropTbl(IP, IP_DESCRIPTION, objName, msg.getMonitorIP(), STRING_TYPE);
//                addBatchInsertPropTbl(SPEED, SPEED_DESCRIPTION, objName, String.valueOf(msg.getAISSpeed()), INTEGER_TYPE);
//                try {
//                    ps.executeBatch();
//                    logger.info("===Inserting Successful===");
//                } catch (Exception ex) {
//                    logger.error("===Inserting failed===");
//                    logger.error(ex.getMessage());
//                }
//            }
//        } catch (SQLException ex) {
//            logger.error(ex.getMessage());
//        } finally {
//            try {
//                rs.close();
//                ps2.close();
//                db.commitTransaction(con);
//            } catch (SQLException ex) {
//                logger.error(ex.getMessage());
//            }
//        }
//
//    }
}
